package com.zcah.db.dbsource.impl;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zcah.db.dbsource.AbstractDbSource;
import com.zcah.db.domain.ColumnProperty;
import com.zcah.db.domain.DbsourceDO;
import com.zcah.db.vo.TableInfo;

public class PostgreSource extends AbstractDbSource {

	private static final Logger logger = LoggerFactory.getLogger(PostgreSource.class);

	private static final String URL = "jdbc:postgresql://%s:%s/%s";

	private static final String SQL_SELECT_TABLES = "select relname, pt.schemaname, "
			+ "cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c "
			+ "join pg_tables pt on relname = pt.tablename "
			+ "where  relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname;";

	private static final String SQL_SHOW_COLUMNS = "SELECT a.attnum,a.attname AS field,t.typname AS type, "
			+ "(SELECT description FROM pg_catalog.pg_description WHERE objoid=a.attrelid AND objsubid=a.attnum) AS descript "
			+ "FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid " + "JOIN pg_type t ON a.atttypid = t.oid "
			+ "LEFT JOIN pg_namespace n ON n.oid = c.relnamespace "
			+ "WHERE c.relname = '%s' and a.attnum > 0 and n.nspname='%s' " + "ORDER BY a.attnum;";

	public PostgreSource(DbsourceDO db) {
		Map<String, String> params = initSource(db);
		String database = params.get("database");

		// pg连接时必须指定数据库名称，并且不能在上下文中切换数据库
		if (database == null || database.isEmpty()) {
			throw new IllegalArgumentException("database不能为空！");
		}
		dbUrl = String.format(URL, db.getIp(), db.getPort(), database);
		try {
			connection = DriverManager.getConnection(dbUrl, db.getUsername(), db.getPassword());
		} catch (SQLException e) {
			errMsg = e.getLocalizedMessage();
		}
	}

	@Override
	public List<TableInfo> getSchemasTables() {
		List<TableInfo> tables = new ArrayList<>();

		try (ResultSet rs = getSqlResultSet(SQL_SELECT_TABLES);) {
			while (rs.next()) {
				TableInfo tableInfo = new TableInfo();
				tableInfo.setTableName(rs.getString("schemaname") + "." + rs.getString("relname"));
				tableInfo.setComment(rs.getString("comment"));
				tables.add(tableInfo);
			}
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage(), e);
		}
		return tables;
	}

	@Override
	public List<ColumnProperty> getTableColumns(String fullTableName) {
		String[] names = fullTableName.split("\\.");
		String sql = String.format(SQL_SHOW_COLUMNS, names[1], names[0]);
		List<ColumnProperty> columnProperties = new ArrayList<>();

		try (ResultSet rs = getSqlResultSet(sql);) {
			while (rs.next()) {
				ColumnProperty columnProperty = new ColumnProperty();
				columnProperty.setId(rs.getInt("attnum"));
				columnProperty.setColumnName(rs.getString("field"));
				columnProperty.setColumnDataType(rs.getString("type"));
				columnProperty.setColumnComment(rs.getString("descript"));
				columnProperties.add(columnProperty);
			}
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage(), e);
		}
		return columnProperties;
	}

}
